﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using BusinessLogicLayer;
using DataAccessLayer;

namespace ServiceLayer
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select Service1.svc or Service1.svc.cs at the Solution Explorer and start debugging.
    public class Service : IService
    {
        static string connection = @"Data Source=(local);Initial Catalog=StationeryInventory;Integrated Security=True";

        //login
        public bool Login(User user)
        {

            String p = null;

            SqlConnection conn = new SqlConnection(connection);

            SqlDataAdapter adapter = new SqlDataAdapter
           ("SELECT Password FROM [User] WHERE UserName = '" + user.UserName + "'", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            if (table.Rows.Count > 0)
            {
                DataRow r = table.Rows[0];
                p = r["Password"].ToString();
            }
            conn.Close();

            if (p != null)
            {

                if (p.Equals(user.Password)) return true;
                else return false;

            }

            return false;
        }

        //welcome
        public Role GetRole(string userName)
        {
            Role role = null;

            int roleID = -1;
            SqlConnection conn = new SqlConnection(connection);
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT RoleID FROM [User] WHERE [UserName] = '" + userName + "'", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            if (table.Rows.Count > 0)
            {
                DataRow r = table.Rows[0];
                roleID = Int32.Parse(r["RoleID"].ToString());
            }
            conn.Close();

            SqlConnection conn1 = new SqlConnection(connection);
            SqlDataAdapter adapter1 = new SqlDataAdapter("SELECT RoleName FROM [Role] WHERE [RoleID] = " + roleID, conn1);
            DataTable table1 = new DataTable();
            adapter1.Fill(table1);
            if (table1.Rows.Count > 0)
            {
                DataRow rr = table1.Rows[0];
                role = new Role();
                role.RoleID = roleID;
                role.RoleName = rr["RoleName"].ToString();

            }

            conn1.Close();
            return role;


        }
        //welcome
        public String GetDept(string userName)
        {

            String deptName = null;

            SqlConnection conn = new SqlConnection(connection);

            SqlDataAdapter adapter = new SqlDataAdapter
           ("select DeptName from Department, Employee, [User] where Department.DeptID = Employee.DeptID and Employee.UserID = [User].UserID and [User].UserID = (" +
            "select UserID from [User] where UserName = '" + userName + "')", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            if (table.Rows.Count > 0)
            {
                DataRow r = table.Rows[0];
                deptName = r["DeptName"].ToString();
            }
            conn.Close();



            return deptName;

        }


        //make requisition
        public Category[] CategoryN()
        {
            List<Category> list = new List<Category>();
            SqlConnection conn = new SqlConnection(connection);
            SqlDataAdapter adapter =
              new SqlDataAdapter("SELECT * FROM [Category]", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            foreach (DataRow r in table.Rows)
            {
                Category c = new Category();
                c.CategoryID = Int32.Parse(r["CategoryID"].ToString());
                c.CategoryName = (string)r["CategoryName"];
                list.Add(c);
            }
            conn.Close();
            return (list.ToArray<Category>());

        }

        //make requisition
        public Stationery[] ItemN(string categoryName)
        {

            int categoryID = -1;
            SqlConnection conn = new SqlConnection(connection);
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT CategoryID FROM [Category] WHERE [CategoryName] = '" + categoryName + "'", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            if (table.Rows.Count > 0)
            {
                DataRow r = table.Rows[0];
                categoryID = Int32.Parse(r["CategoryID"].ToString());
            }
            conn.Close();

            List<Stationery> list = new List<Stationery>();
            SqlConnection conn1 = new SqlConnection(connection);
            SqlDataAdapter adapter1 = new SqlDataAdapter("SELECT * FROM [Stationery]  WHERE [CategoryID] = " + categoryID, conn1);
            DataTable table1 = new DataTable();
            adapter1.Fill(table1);
            foreach (DataRow r in table1.Rows)
            {

                Stationery c = new Stationery();
                c.ItemCode = (string)r["ItemCode"].ToString();
                c.CategoryID = Int32.Parse(r["CategoryID"].ToString());
                c.ItemName = (string)r["ItemName"];
                c.UOM = (string)r["UOM"];
                c.ReorderLevel = Int32.Parse(r["ReorderLevel"].ToString());
                c.ReorderQty = Int32.Parse(r["ReorderQty"].ToString());
                c.AvailableQty = Int32.Parse(r["AvailableQty"].ToString());
                c.BinNo = (string)r["BinNo"];

                list.Add(c);
            }
            conn.Close();
            return (list.ToArray<Stationery>());

        }

        //make requisition
        public bool ReqSubmit(string userName, Req[] reqList)
        {
            if (reqList[1].Quantity == 20)

                return true;

            return false;
        }
        //test
        public User[] List()
        {
            List<User> list = new List<User>();
            SqlConnection conn = new SqlConnection(connection);
            SqlDataAdapter adapter =
              new SqlDataAdapter("SELECT * FROM [User]", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);
            foreach (DataRow r in table.Rows)
            {

                User c = new User();
                c.UserID = Int32.Parse(r["UserID"].ToString());
                c.UserName = (string)r["UserName"];
                c.Password = (string)r["Password"];
                c.RoleID = Int32.Parse(r["RoleID"].ToString());

                list.Add(c);
            }
            conn.Close();
            return (list.ToArray<User>());
        }
        //test
        public bool LC(User[] list)
        {
            if (list[0].UserID == list[1].UserID)
            {

                return true;

            }

            return false;
        }


    }
}
